use crate::db::entities::task_knowledge::{ActiveModel, CREATE_QUERY, Column, Entity, Model, TABLE_COLUMNS, TABLE_NAME, get_column};
use anyhow::anyhow;
use anyhow::{Ok, Result};
use log::info;
use rbase::db::{self, get_connection};
use rbase::id_utils::gen_id;
use rbase::time_utils::get_systemtime_millis;
use sea_orm::ActiveValue::Set;
use sea_orm::EntityTrait;
use sea_orm::QueryFilter;
use sea_orm::prelude::Expr;
use sea_orm::{ColumnTrait, Order, PaginatorTrait, QueryOrder, QuerySelect};

#[derive(Default)]
pub struct SearchParams {
    pub task_id: Option<i64>,
    pub knowledge_id: Option<i64>,
    pub offset: Option<u64>,
    pub limit: Option<u64>,
    pub order_by: Option<String>,
    pub order: Option<String>,
}

pub async fn init_tables() -> Result<()> {
    info!("Initializing tables");
    db::init_tables(TABLE_NAME, CREATE_QUERY, &TABLE_COLUMNS).await?;
    Ok(())
}

pub async fn insert_task_knowledge(model: Model) -> Result<Model> {
    let connection = get_connection()?;

    let mut active_model = ActiveModel::from(model);

    let id = gen_id()?;
    active_model.id = Set(id);
    let systemtime = get_systemtime_millis();
    active_model.created_at = Set(Some(systemtime));
    active_model.modified_at = Set(Some(systemtime));

    Entity::insert(active_model).exec(&connection).await?;
    let model = find_by_id(id).await?.ok_or_else(|| anyhow!("Failed to insert task knowledge"))?;
    Ok(model)
}

pub async fn find_by_task_id_and_knowledge_id(task_id: i64, knowledge_id: i64) -> Result<Option<Model>> {
    let connection = get_connection()?;

    Entity::find()
        .filter(Column::TaskId.eq(task_id))
        .filter(Column::KnowledgeId.eq(knowledge_id))
        .one(&connection)
        .await
        .map_err(|e| anyhow!("Error finding task knowledge: {:?}", e))
}

pub async fn find_by_id(id: i64) -> Result<Option<Model>> {
    let connection = get_connection()?;

    Entity::find()
        .filter(Column::Id.eq(id))
        .one(&connection)
        .await
        .map_err(|e| anyhow!("Error finding task knowledge: {:?}", e))
}

pub async fn search(search_params: &SearchParams) -> Result<(Vec<Model>, i64)> {
    let connection = get_connection()?;

    let mut filter = Expr::value(true);
    let mut has_filter = false;

    if let Some(task_id) = search_params.task_id {
        filter = filter.and(Column::TaskId.eq(task_id));
        has_filter = true;
    }

    if let Some(knowledge_id) = search_params.knowledge_id {
        filter = filter.and(Column::KnowledgeId.eq(knowledge_id));
        has_filter = true;
    }

    // 计算总数
    let count = if has_filter { Entity::find().filter(filter.clone()) } else { Entity::find() };
    let total = count.count(&connection).await?;
    if total <= 0 {
        return Ok((Vec::new(), 0));
    }

    // 分页计算
    let mut query = if has_filter { Entity::find().filter(filter) } else { Entity::find() };
    if let Some(offset) = search_params.offset {
        query = query.offset(offset);
    }
    if let Some(limit) = search_params.limit {
        query = query.limit(limit);
    }
    if let Some(order_by) = &search_params.order_by {
        let col = get_column(order_by);
        let order = match search_params.order.clone() {
            Some(order) => match order.as_str() {
                "asc" | "ascending" => Order::Asc,
                _ => Order::Desc,
            },
            None => Order::Desc,
        };
        query = query.order_by(col, order);
    }

    let models = query.all(&connection).await?;
    Ok((models, total as i64))
}
